package com.kcsj.dao;
//201902104050 李延杰

import com.kcsj.domain.Inventory;
import com.kcsj.domain.Product;
import com.kcsj.domain.ProductModel;
import com.kcsj.domain.Warehouse;
import com.kcsj.service.InventoryService;
import com.kcsj.service.ProductService;
import com.kcsj.util.Condition;
import com.kcsj.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

public class ProductModelDao {
    public static ProductModelDao productModelDao = new ProductModelDao();
    private ProductModelDao(){}
    public static ProductModelDao getInstance() {return productModelDao;}

    public ProductModel find(String productModelNo, Connection conn) throws SQLException {
        ProductModel desiredProductModel = null;
        //创建SQL语句
        String search = "SELECT * from ProductModel where productModelNo = ?";
        //在连接上创建语句盒子对象
        PreparedStatement pstmt = conn.prepareStatement(search);
        pstmt.setString(1, productModelNo);
        //执行SQL语句
        ResultSet results = pstmt.executeQuery();
        if (results.next()) {
            String description = results.getString("description");
            String remarks = results.getString("remarks");
            String materials = results.getString("materials");
            double price = results.getDouble("price");
            double volume = results.getDouble("volume");
            double weight = results.getDouble("weight");
            int rest = results.getInt("rest");
            Product product =ProductDao.getInstance().
                    find(results.getString("productNo"),conn);
            Collection<Warehouse> warehouses = InventoryDao.getInstance().findByProductNo(conn, productModelNo);
            desiredProductModel = new ProductModel(productModelNo,
                    description,price,remarks,volume,weight,
                    materials,product,warehouses,rest);
        }
            return desiredProductModel;
        }

    public Collection<ProductModel> findByProductNo(String productNo, Connection conn) throws SQLException {
        Collection<ProductModel> productModels= new ArrayList<>();
        //创建SQL语句
        String search = "SELECT * from ProductModel where productNo = ?";
        //在连接上创建语句盒子对象
        PreparedStatement pstmt = conn.prepareStatement(search);
        pstmt.setString(1, productNo);
        //执行SQL语句
        ResultSet results = pstmt.executeQuery();
        while (results.next()) {
            String productModelNo = results.getString("productModelNo");
            String description = results.getString("description");
            double price = results.getDouble("price");
            String remarks = results.getString("remarks");
            double volume = results.getDouble("volume");
            double weight = results.getDouble("weight");
            String materials = results.getString("materials");
            Product product = ProductDao.getInstance().find(productNo, conn);
            int rest = results.getInt("rest");
            Collection<Warehouse> warehouses = InventoryDao.getInstance().findByProductNo(conn, productModelNo);
            ProductModel desiredProductModel = new ProductModel(productModelNo,
                    description,price,remarks,volume,weight,
                    materials,product,warehouses,rest);
            productModels.add(desiredProductModel);
        }
        return productModels;
    }
    //查找school表中所有数据
    public Collection<ProductModel> findAll(List<Condition> conditionList
            , Connection conn) throws SQLException {
        ArrayList<ProductModel> deliveryPeoples = new ArrayList<>();
        //预编译方式进行查询
        StringBuilder sql = new StringBuilder("select * from productmodel");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql.append(whereClause);
        }

        PreparedStatement preparedStatement = conn.prepareStatement(sql.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            String productModelNo = resultSet.getString(1);
            String description = resultSet.getString(2);
            int price = resultSet.getInt(3);
            String remarks = resultSet.getString(4);
            int volume = resultSet.getInt(5);
            int weight = resultSet.getInt(6);
            String materials = resultSet.getString(7);
            String  productNo = resultSet.getString(8);
            Product product = ProductService.getInstance().find(productNo);
            int rest = resultSet.getInt("rest");
            Collection<Warehouse> warehouses = InventoryDao.getInstance().findByProductNo(conn, productModelNo);
            ProductModel productModel = new ProductModel(productModelNo,description,price,remarks,volume,
                    weight,materials,product,warehouses,rest);
            deliveryPeoples.add(productModel);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return deliveryPeoples;
    }

    public boolean update(ProductModel productModel, Connection conn) throws SQLException {
        //预编译方式进行修改数据
        String s = "update productmodel set description=?,price=?,remarks=?,volume=?," +
                "weight=?,materials=? where productModelNo = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,productModel.getDescription());
        preparedStatement.setDouble(2,productModel.getPrice());
        preparedStatement.setString(3,productModel.getRemarks());
        preparedStatement.setDouble(4,productModel.getVolume());
        preparedStatement.setDouble(5,productModel.getWeight());
        preparedStatement.setString(6,productModel.getMaterials());
        preparedStatement.setString(7,productModel.getProductModelNo());
        //如果执行影响行数大于0，则修改成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }
    //向Administrator表中添加数据
    public int add(ProductModel productModel, Connection conn) throws SQLException {
        //预编译方式进行增加数据
        String s = "insert into productmodel(productModelNo,description,price,remarks,volume, weight,materials,productNo) value (?,?,?,?,?,?,?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,productModel.getProductModelNo());
        preparedStatement.setString(2,productModel.getDescription());
        preparedStatement.setDouble(3,productModel.getPrice());
        preparedStatement.setString(4,productModel.getRemarks());
        preparedStatement.setDouble(5,productModel.getVolume());
        preparedStatement.setDouble(6,productModel.getWeight());
        preparedStatement.setString(7,productModel.getMaterials());
        preparedStatement.setString(8,productModel.getProduct().getProductNo());
        //如果执行影响行数大于0，则增加成功
        int id = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return id;
    }

    //在school表根据id删除相应数据
    public boolean delete(String productModelNo, Connection conn) throws SQLException {
        //预编译方式进行删除数据
        String s = "delete from productmodel where  productModelNo = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1, productModelNo);
        //如果执行影响行数大于0，则增加成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }

    public boolean changeRest(String productModelNo, Integer count, Connection conn) throws SQLException {
        String sql = "UPDATE productModel SET rest= rest+" + count + " WHERE productModelNo=?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1,productModelNo);
        int affectedRows = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        return affectedRows == 1;
    }
}
